﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Windows.Forms;

namespace StuManageSys
{
    internal class sqliteController
    {
        static string connectUri = "Data Source=StudentManager.db;Version=3;";

        // 创建Table, 包括学生表，班级表，学校表，
        public static void createTable()
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            try
            {
                conn.Open();

                string[] sqlStatements =
                {
                    "CREATE TABLE IF NOT EXISTS school(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, type TEXT, address TEXT)",
                    "CREATE TABLE IF NOT EXISTS class (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, school_id INTEGER, grade TEXT, FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE CASCADE)",
                    "CREATE TABLE IF NOT EXISTS student (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, student_id TEXT NOT NULL, class_id INTEGER, school_id INTEGER, FOREIGN KEY (class_id) REFERENCES class(id) ON DELETE CASCADE, FOREIGN KEY (school_id) REFERENCES school(id) ON DELETE CASCADE)"
                };

                foreach (string sql in sqlStatements)
                {
                    SQLiteCommand command = new SQLiteCommand(sql, conn);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("创建表时发生错误: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        // 创建LOG表，用户的所有操作记录都在这里记录
        public static void createLogTable()
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);
            try
            {
                conn.Open();
                string sql =
                    "CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY AUTOINCREMENT, operation TEXT, time TEXT)";
                SQLiteCommand command = new SQLiteCommand(sql, conn);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("创建表时发生错误: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        // 添加日志
        public static void addLog(string operation)
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            string sql = "INSERT INTO log(operation, time) VALUES(@operation, @time)";
            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@operation", operation);
            command.Parameters.AddWithValue("@time", DateTime.Now.ToString());

            conn.Open();
            command.ExecuteNonQuery();

            conn.Close();
        }

        // 查询日志：返回所有日志
        public static List<string> queryLog()
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            List<string> logList = new List<string>();

            string sql = "SELECT * FROM log";
            SQLiteCommand command = new SQLiteCommand(sql, conn);

            conn.Open();

            SQLiteDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                string log = reader["operation"].ToString() + " " + reader["time"].ToString();
                logList.Add(log);
            }

            conn.Close();

            return logList;
        }

        // 添加学校信息
        public static void addSchool(string name, string type, string address)
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            string sql = "INSERT INTO school(name, type, address) VALUES(@name, @type, @address)";
            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@name", name);
            command.Parameters.AddWithValue("@type", type);
            command.Parameters.AddWithValue("@address", address);
            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
            // 添加日志
            addLog(sql);
        }

        // 添加班级信息
        public static void addClass(string name, string school_name, string grade)
        {
            // 根据school_name找到schoolid
            int schoolId = getSchoolId(school_name);
            if (schoolId < 0)
            {
                throw new Exception("学校不存在！请检查输入是否正确！");
            }

            SQLiteConnection conn = new SQLiteConnection(connectUri);

            string sql = "INSERT INTO class(name, school_id, grade) VALUES(@name, @school_id, @grade)";
            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@name", name);
            command.Parameters.AddWithValue("@school_id", schoolId);
            command.Parameters.AddWithValue("@grade", grade);

            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
            // 添加日志
            addLog(sql);
        }

        // 根据学校名称获取学校id
        public static int getSchoolId(string schoolName)
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            string sql = "SELECT id FROM school WHERE name = @name";
            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@name", schoolName);

            conn.Open();

            SQLiteDataReader reader = command.ExecuteReader();

            int schoolId = -1;

            while (reader.Read())
            {
                schoolId = int.Parse(reader["id"].ToString());
            }

            conn.Close();

            return schoolId;
        }

        // 根据班级名称获取班级ID
        public static int getClassId(string className)
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            string sql = "SELECT id FROM class WHERE name = @name";
            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@name", className);

            conn.Open();

            SQLiteDataReader reader = command.ExecuteReader();

            int classId = -1;

            while (reader.Read())
            {
                classId = int.Parse(reader["id"].ToString());
            }

            conn.Close();

            return classId;
        }

        // 添加学生信息
        public static void addStudent(string name, string studentId, string className, string schoolName)
        {
            // 根据班级名称获取班级ID
            int classId = getClassId(className);
            if (classId < 0)
            {
                throw new Exception("班级不存在！请检查输入是否正确！");
            }

            // 根据学校名称获取学校ID
            int schoolId = getSchoolId(schoolName);
            if (schoolId < 0)
            {
                throw new Exception("学校不存在！请检查输入是否正确！");
            }

            SQLiteConnection conn = new SQLiteConnection(connectUri);

            string sql =
                "INSERT INTO student(name, student_id, class_id, school_id) VALUES(@name, @student_id, @class_id, @school_id)";
            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@name", name);
            command.Parameters.AddWithValue("@student_id", studentId);
            command.Parameters.AddWithValue("@class_id", classId);
            command.Parameters.AddWithValue("@school_id", schoolId);

            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
        }

        // 返回所有学校信息
        public static List<string> querySchool()
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            List<string> schoolList = new List<string>();

            string sql = "SELECT * FROM school";
            SQLiteCommand command = new SQLiteCommand(sql, conn);

            conn.Open();

            SQLiteDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                string school = reader["name"].ToString() + " " + reader["type"].ToString() + " " +
                                reader["address"].ToString();
                schoolList.Add(school);
            }

            conn.Close();

            return schoolList;
        }

        // 返回所有班级信息
        public static List<string> queryClass()
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            List<string> classList = new List<string>();

            string sql = "SELECT * FROM class";
            SQLiteCommand command = new SQLiteCommand(sql, conn);

            conn.Open();

            SQLiteDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                string school = reader["name"].ToString() + " " + reader["grade"].ToString();
                classList.Add(school);
            }

            conn.Close();

            return classList;
        }

        // 返回所有学生信息
        public static List<string> queryStudent()
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            List<string> studentList = new List<string>();

            string sql = "SELECT * FROM student";

            SQLiteCommand command = new SQLiteCommand(sql, conn);
            conn.Open();
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string student = reader["name"].ToString() + " " + reader["student_id"].ToString() + " " +
                                 reader["class_id"].ToString() + " " + reader["school_id"].ToString();
                studentList.Add(student);
            }

            conn.Close();

            return studentList;
        }

        // 根据名称删除学校
        public static void delSchool(string name)
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            string sql = "DELETE FROM school WHERE name = @name";

            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@name", name);

            conn.Open();

            command.ExecuteNonQuery();

            conn.Close();

            // 添加日志
            addLog(sql);
        }

        // 根据名称删除班级
        public static void delClass(string name)
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            string sql = "DELETE FROM class WHERE name = @name";

            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@name", name);

            conn.Open();

            command.ExecuteNonQuery();

            conn.Close();

            // 添加日志
            addLog(sql);
        }

        public static void delStudent(string name)
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            string sql = "DELETE FROM student WHERE name = @name";

            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@name", name);

            conn.Open();

            command.ExecuteNonQuery();

            conn.Close();

            // 添加日志
            addLog(sql);
        }

        // 修改对应姓名的学生的学号和班级
        public static void editStudent(string name, string studentId, string className)
        {
            SQLiteConnection conn = new SQLiteConnection(connectUri);

            int classId = getClassId(className);

            string sql = "UPDATE student SET student_id = @student_id, class_id = @class_id WHERE name = @name";

            SQLiteCommand command = new SQLiteCommand(sql, conn);

            command.Parameters.AddWithValue("@student_id", studentId);
            command.Parameters.AddWithValue("@class_id", classId);
            command.Parameters.AddWithValue("@name", name);

            conn.Open();
        }
    }
}